 
  

 






<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 
<html>

<!-- Mirrored from www.javapractices.com/topic/TopicAction.do?Id=105 by HTTrack Website Copier/3.x [XR&CO'2010], Sun, 12 Jun 2011 17:28:07 GMT -->
<!-- Added by HTTrack --><meta http-equiv="content-type" content="text/html;charset=UTF-8"><!-- /Added by HTTrack -->
<head>
 <title>
  Java Practices -> Keep SQL out of code
 </title>
 <link rel="stylesheet" type="text/css" href="../stylesheet8.css" media="all">
 
 <link rel="shortcut icon" href='../images/favicon.ico' type="image/vnd.microsoft.icon">
 <meta name="description" content="Concise presentations of java programming practices, tasks, and conventions, amply illustrated with syntax highlighted code examples.">
 
 <meta name='keywords' content='JDBC,MessageFormat,metadata,Properties,text,database,SQL,java,java programming,java practices,java idiom,java style,java design patterns,java coding conventions,'>
 
 
</head>
 
<body>


<div class='menu-bar'>
 
  <a href='../home/HomeAction.html' title='Table of Contents'>Home</a> |
  <a href='../vote/VoteSummaryAction-2.html' title='View Poll Results'>Poll</a> |
   
  <A href='../feedback/FeedbackAction451f-2.html?Operation=Show' title='Send Your Feedback'>Wiki</a> |
  <b><a href='../source/SourceAction-2.html' title='Grab Source Code'>Source Code</a></b><IMG class='no-margin' SRC="../images/goldstar.gif" ALT=""> |

  <a href='http://www.web4j.com/Java_Web_Application_Framework_Overview.jsp?From=1' title='Free Download - Java Web Application Framework'><b>WEB4J</b></a> |
  
  <a href='http://www.date4j.net/' title='Replacement for java.util.Date'><b>DATE4J</b></a> |

   <a href='../references/ReferencesAction-2.html' title='References'>Links</a>
   
  <form action='http://www.javapractices.com/search/SearchAction.do' method='get' class='search-form'>
   <input type='text' name='SearchTerms' value="" size=12 maxlength=50 class='search'>
   <input type='submit' value="Search">
  </form>
 
</div>

<P>



  

 






<p class="display-messages">

 

 

</p>


<div class="main-layout">
 
   

 




<div class='page-title'>Keep SQL out of code</div>

<div class='main-body'>
 
<br>Storing SQL as text, outside of compiled code, eases maintenance significantly
:
<ul>
<li>
it minimizes the ripple effects caused by changes to the database. For
example, if a table name or column name changes, the fix is usually an
easy search and replace in a single text file.</li>

<li>
for developers new to a project, having SQL in separate files apart from code makes it significantly
easier to browse through SQL statements and learn the database structure</li>

<li>
if an older application needs to be migrated to a new framework, then the
SQL is readily available for porting to the new application</li>

<li>
if necessary, changes to SQL can even be made after deployment, by changing
the text file and doing a restart or a refresh. This can occasionally be
very useful. For example, if users ask for a change in the sort order of
some data, then the developer very likely <em>has the option</em> of implementing
that change without a redeployment (and maybe even without a restart, if
the tools support a refresh). However, some organizations would object to 
performing such changes directly in production. </li>

<li>
adding support for a new relational database may become simply a matter
of translating only those statements that happen to be non-portable.</li>

</ul>
<i>The Pragmatic Programmer</i>, by Hunt and Thomas, emphasizes this type
of metadata, saying that :
<p>"<i>It forces you to create a more robust, abstract design by deferring
details - deferring them all the way out of the program.</i>"

<P>There is a possible objection to keeping SQL as text in a production environment :
it can be examined for vulnerabilities by a malicious user with access to the machine where it resides. 
In that case, tools should allow a developer the option of translating the 
SQL-as-text (used in development) into some binary form (used in production).


<p>Many SQL statements come in these three forms, all of which may be removed
from compiled code and stored outside the program as text :
<p><b>Fixed text&nbsp; :</b>
<p><tt>SELECT * FROM Board ORDER BY Id;</tt>
<p>Here, simply fetch a <tt>String</tt> from some textual source (such
as a properties file), and pass it to the constructor of a 
<tt><a href="http://java.sun.com/javase/6/docs/api/java/sql/Statement.html">Statement</a></tt>
or <tt><a href="http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html">PreparedStatement</a></tt>.
This form is likely relatively rare, since most SQL statements seem to
have at least one parameter.

<p><b>Parameterized, with a single set of values :</b>
<p><tt>INSERT INTO Board</tt>
<br><tt>(Id, Title, TimeZone, Language, CreationDate)</tt>
<br><tt>VALUES ('quark','High Energy Physics','GMT','english',NOW());</tt>
<p>Here, fetch a <tt>String</tt> having '?' as place holders from some
textual source :
<p><tt>INSERT INTO Board</tt>
<br><tt>(Id, Title, TimeZone, Language, CreationDate)</tt>
<br><tt>VALUES (?,?,?,?,NOW());</tt>
<p>This <tt>String</tt> is then passed to the constructor of a <tt>PreparedStatement</tt>,
and parameters are inserted using the methods of the <tt>PreparedStatement</tt>
class. Note that 
<tt><a href="http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html">PreparedStatement</a></tt> 
has <a href='TopicActiona657-2.html'>significant advantages</a> over an ordinary 
<tt><a href="http://java.sun.com/javase/6/docs/api/java/sql/Statement.html">Statement</a></tt>.

<p><b>Parameterized, with multiple sets of parameters :</b>
<p><tt>INSERT INTO Vote</tt>
<br><tt>VALUES</tt>
<br><tt>('RASB', 3, 'Y', NOW()),</tt>
<br><tt>('RASB', 2, 'Y', NOW()),</tt>
<br><tt>('RASB', 1, 'Y', NOW()),</tt>
<br><tt>('RASB', 4, '?', NOW());</tt>
<p>Using such a statement to add many records is advantageous, since it
involves only one trip to the database. However, the 
<tt><a href="http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html">PreparedStatement</a></tt>
parameter mechanism cannot do this in one statement, since the number of
added records is arbitrary. The usual style in this case is to use batched
statements, in which a number of individual INSERT statements are collected
together in a batch, and then executed as a unit.

<p><b>Other cases :</b>
<p>There are cases in which it remains preferable to construct an SQL statement
(or at least parts of it) in code. This is particularly true for many <em>search operations</em>, 
where the end user enters various search criteria into a form used to construct 
<tt>WHERE</tt> and <tt>ORDER BY</tt> clauses.
It is often the case that a very large number of possible <tt>WHERE</tt> and <tt>ORDER BY</tt> clauses 
can be built from such user input. Enumerating <em>all</em> such possibilities is often 
undesirable. In such cases, building the SQL statement dynamically from user input seems 
preferable.

<P>When constructing statements dynamically, however, you must guard against 
<a href="http://en.wikipedia.org/wiki/SQL_Injection">SQL injection attacks</a>. 
Here is a simple technique for doing so :
<ul>
<li>dynamically build the <em>core</em> SQL string in code, but, at this first stage, do not insert the data 
parameters. That is, build a String containing '?' placeholders for data, in the usual way  
you build a SQL string for <tt>PreparedStatement</tt>. 
<li>create a <tt>PreparedStatement</tt> with the given SQL string. This will verify that the 
SQL statement is of valid form. As always, using <tt>PreparedStatement</tt> in this way will
<a href='TopicActiona657-2.html'>protect against SQL injection attacks</a>.
<li>inject the data for the '?' placeholders in the usual way.
</ul>

With the above technique, user input of search criteria is used for <em>two</em> distinct purposes : 
<em>first</em> dynamically construct the core SQL with the desired <tt>WHERE</tt> and <tt>ORDER BY</tt> 
clauses (containing '?' placeholders), 
and <em>secondly</em> populate the '?' placeholders with data in the usual way.


<p><b>Example</b>
<p>Here is an example of a <tt>.sql</tt> file which contains the SQL statements related to a single feature.
All parameterized statements are expressed in a
style suitable for a 
<tt><a href="http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html">PreparedStatement</a></tt>,
to avoid using any quoting
conventions. It is taken from the <a href='TopicAction5f31-2.html'>WEB4J</a> example application.

<PRE>
LIST_RESTOS {
 SELECT Id, Name, Location, Price, Comment
 FROM Resto
 ORDER BY Name
}

FETCH_RESTO {
 SELECT Id, Name, Location, Price, Comment
 FROM Resto
 WHERE Id =? 
}

ADD_RESTO  {
  -- Id is an autoincrement field, populated automagically by the database.
 INSERT INTO Resto (Name, Location, Price, Comment) VALUES (?,?,?,?)
}

CHANGE_RESTO {
  UPDATE Resto SET Name=?, Location=?, Price=?, Comment=? WHERE Id=?
}

DELETE_RESTO {
  DELETE FROM RESTO WHERE Id=?
}
</PRE>

<p>In the case of web applications, such files are safely placed in the
<tt>WEB-INF</tt>
directory, where they are not accessible to the user, and can be retrieved
using <tt><a href="http://java.sun.com/j2ee/1.4/docs/api/javax/servlet/ServletContext.html#getResourceAsStream(java.lang.String)">ServletContext.getResourceAsStream</a></tt>.
<p>The <a href="TopicAction5f31-2.html">WEB4J</a> framework places all SQL statements
in one or more such <tt>.sql </tt>files, located anywhere under the <tt>WEB-INF</tt>
directory. (Please see the javadoc for its data layer for more information.)
<br>
<br>

</div>




<div class='topic-section'>See Also :</div>
<div class='main-body'>
 
  
  <a href='TopicAction564b-2.html?Id=66'>Data access objects</a> <br>
 
  
  <a href='TopicAction0d22-2.html?Id=76'>Reduce database code duplication</a> <br>
 
  
  <a href='TopicActionb57d-2.html?Id=172'>Consider using standard SQL</a> <br>
 
  
  <a href='TopicAction5f31-2.html?Id=188'>A Web App Framework - WEB4J</a> <br>
 
  
  <a href='TopicActiona657-2.html?Id=212'>Prefer PreparedStatement</a> <br>
 
</div>


<div class='topic-section'>Would you use this technique?</div>
<div class='main-body'>
  
  <form action="http://www.javapractices.com/vote/AddVoteAction.do" method='post'>
    Yes<input type='radio' name='Choice' value='Y' >
    &nbsp;&nbsp;No<input type='radio' name='Choice' value='N'>
    &nbsp;&nbsp;Undecided<input type='radio' name='Choice' value="?" >
    &nbsp;&nbsp;<input type=submit value="Vote" >
    <input type='hidden' name='Operation' value='Apply'>
    <input type='hidden' name='TopicId' value='105'>
  </form>
</div>

<div style='height:10.0em;'></div>

 
 
</div>

  

 





<div align='center' class='legalese'>  
&copy; 2011 Hirondelle Systems |
<a href='../source/SourceAction-2.html'><b>Source Code</b></a><IMG class='no-margin' SRC="../images/goldstar.gif" ALT=""> |
<a href="mailto:webmaster@javapractices.com">Contact</a> |
<a href="http://creativecommons.org/licenses/by-nc-sa/1.0/">License</a> |
<a href='../apps/cjp.rss'>RSS</a>
<!-- ukey="2AC36CD2" -->
<!-- ckey="16DF3D87" -->
<br>

 Individual code snippets can be used under this <a href='../LICENSE.txt'>BSD license</a> - Last updated on June 6, 2010.<br>
 Over 150,000 unique IPs last month - <span title='Java Practices 2.6.5, Mon May 16 00:00:00 EDT 2011'>Built with</span> <a href='http://www.web4j.com/'>WEB4J</a>.<br>
 - In Memoriam : Bill Dirani -
</div>

<script src="../../www.google-analytics.com/urchin.js" type="text/javascript">
</script>
<script type="text/javascript">
_uacct = "UA-2633428-1";
urchinTracker();
</script>



</body>

<!-- Mirrored from www.javapractices.com/topic/TopicAction.do?Id=105 by HTTrack Website Copier/3.x [XR&CO'2010], Sun, 12 Jun 2011 17:28:07 GMT -->
<!-- Added by HTTrack --><meta http-equiv="content-type" content="text/html;charset=UTF-8"><!-- /Added by HTTrack -->
</html>
